package com.zhx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhx.database.DbUtil;
import com.zhx.role.Role;
import com.zhx.vo.Page;


public class RoleDao extends BaseDao {
	
	public RoleDao(){
		
	}
	

	@Override
	protected Role handle(ResultSet rs) throws SQLException {
		try{
			Role role=new Role();
			role.setId(rs.getInt("id"));
			role.setRoleId(rs.getString("role_id"));
			role.setRoleName(rs.getString("role_name"));
			role.setCreateTime(rs.getTimestamp("create_time"));
			role.setUpdateTime(rs.getTimestamp("update_time"));
			return role;
		}catch(SQLException e){
			
			throw new SQLException("结果集转为实例失败!",e);
		}
	}
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Role> queryOjects(String sql,Object[]params)throws SQLException{
		Connection conn =null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Role> results=new ArrayList<Role>();
		try{
			conn=DbUtil.getConn();
			pstmt=conn.prepareStatement(sql);
			int index=1;
			if(params!=null){
			  for(Object param:params){
				  if(param instanceof String){
					  pstmt.setString(index++, (String)param);
				  }
				  if(param instanceof Integer){
					  pstmt.setInt(index++, (Integer)param);
				  }
				  if(param instanceof Float){
					  pstmt.setFloat(index++, (Float)param);
				  }
			  }
			}
			rs=pstmt.executeQuery();
			while(rs.next()){

				Role role=handle(rs);
				results.add(role);
			
			}
			return results;
		}catch(SQLException e){
			throw new SQLException("执行SQL["+sql+"]失败",e);
		}
		
	}
	
/**
 * 分页
 * @param sql
 * @param params
 * @param page
 * @param size
 * @return
 * @throws SQLException
 */
	public Page queryOjectsByPage(String sql,Object[]params,int pageNo,int size)throws SQLException{
		Connection conn =null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Page page=null;
		List<Role> datas=new ArrayList<Role>();
		try{
			int start=(pageNo-1)*size;
			conn=DbUtil.getConn();
			pstmt=conn.prepareStatement(sql+" limit "+start+","+size);
			int index=1;
			if(params!=null){
			  for(Object param:params){
				  if(param instanceof String){
					  pstmt.setString(index++, (String)param);
				  }
				  if(param instanceof Integer){
					  pstmt.setInt(index++, (Integer)param);
				  }
				  if(param instanceof Float){
					  pstmt.setFloat(index++, (Float)param);
				  }
			  }
			}
			rs=pstmt.executeQuery();
			
			RoleMenuDao roleMenuDao = new RoleMenuDao();
			while(rs.next()){
				Role role=handle(rs);
				role.setMenus(roleMenuDao.getMenusByRoleId(role.getRoleId()));
				datas.add(role);
			}
			//统计SQL对应记录数
			int total=count(sql, params);
			page=new Page(total, pageNo, size, datas);
			return page;
		}catch(SQLException e){
			throw new SQLException("执行SQL["+sql+"]失败",e);
		}finally{
			DbUtil.close(rs, pstmt, conn);
		}
		
	}
	
	/**
	 * 添加角色信息
	 * @param role
	 * @throws SQLException
	 */
	public void add(Role role) throws SQLException {
		if(role != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"insert into sys_role(role_id, role_name, create_time, update_time)");
			sqlBuff.append("values(?,?,?,?)");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setString(1, role.getRoleId());
				pstmt.setString(2, role.getRoleName());
				pstmt.setTimestamp(3, role.getCreateTime());
				pstmt.setTimestamp(4, role.getUpdateTime());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}
		}
	}
	
	/**
	 * 根据ID获取角色信息
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Role getRoleById(String id) throws SQLException {
		Role role = new Role();
		if(id != null) {
			DbUtil dbUtil = new DbUtil();
			
			String sql = "select * from sys_role where id = '"+ id +"'";
			ResultSet rs = dbUtil.executeQuery(sql);
			while (rs.next()) {
				role = handle(rs);
				RoleMenuDao roleMneuDao = new RoleMenuDao();
				role.setMenus(roleMneuDao.getMenusByRoleId(role.getRoleId()));
			}
		}
		return role;
	}
	
	/**
	 * 更新角色信息
	 * @param role
	 * @throws SQLException
	 */
	public void updateRole(Role role) throws SQLException {
		if(role != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"update sys_role set role_id = ?, role_name = ?,  update_time = ? where id = ?");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setString(1, role.getRoleId());
				pstmt.setString(2, role.getRoleName());
				pstmt.setTimestamp(3, role.getUpdateTime());
				pstmt.setInt(4, role.getId());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}
		}
	}
	
	public void delete(Role role) throws SQLException {
		if(role != null) {
			DbUtil dbUtil = new DbUtil();
			PreparedStatement pstmt = null;
			Connection conn = dbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer(
					"delete from sys_role where id = ?");
			try {
				pstmt = conn.prepareStatement(sqlBuff.toString());
				pstmt.setInt(1, role.getId());
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbUtil.close(pstmt);
			}
		}
	}
	

}
